package com.demo.youxuanmall.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.demo.youxuanmall.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
    
    /**
     * 获取指定用户的订单
     */
    @Select("SELECT * FROM `order` WHERE user_id = #{userId} AND is_deleted = 0 ORDER BY create_time DESC")
    List<Order> getUserOrders(@Param("userId") Long userId);
    
    /**
     * 统计指定状态的订单数量
     */
    @Select("SELECT COUNT(*) FROM `order` WHERE status = #{status} AND is_deleted = 0")
    Integer countOrdersByStatus(@Param("status") Integer status);
    
    /**
     * 获取总销售额
     */
    @Select("SELECT SUM(payment) FROM `order` WHERE status != 4 AND is_deleted = 0")
    BigDecimal getTotalSales();
    
    /**
     * 获取每日销售趋势
     */
    @Select("SELECT DATE(create_time) as date, SUM(payment) as sales, COUNT(*) as orders " +
            "FROM `order` " +
            "WHERE create_time >= #{startDate} AND create_time <= #{endDate} AND status != 4 AND is_deleted = 0 " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY date")
    List<Map<String, Object>> getSalesTrendByDay(@Param("startDate") String startDate, @Param("endDate") String endDate);
    
    /**
     * 获取分类销售占比
     */
    @Select("SELECT c.name as category, SUM(oi.total_price) as sales, " +
            "ROUND(SUM(oi.total_price) / (SELECT SUM(total_price) FROM order_item) * 100, 2) as percentage " +
            "FROM order_item oi " +
            "JOIN product p ON oi.product_id = p.id " +
            "JOIN category c ON p.category_id = c.id " +
            "GROUP BY c.id " +
            "ORDER BY sales DESC")
    List<Map<String, Object>> getCategorySalesPercentage();
    
    /**
     * 获取热门商品
     */
    @Select("SELECT p.id, p.name, p.price, SUM(oi.quantity) as sales, " +
            "SUM(oi.total_price) as sales_amount, p.main_image as image " +
            "FROM order_item oi " +
            "JOIN product p ON oi.product_id = p.id " +
            "JOIN `order` o ON oi.order_id = o.id " +
            "WHERE o.status != 4 AND o.is_deleted = 0 " +
            "GROUP BY p.id " +
            "ORDER BY sales DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getHotProducts(@Param("limit") Integer limit);
} 